set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_route_lastest_base_dt
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,concat_ws('-',in_from_name,in_collect_name,extra_in_collect_name,in_to_name,e1_end_name,e2_end_name,e3_end_name,e4_end_name,e5_end_name,e6_end_name,out_collect_name,extra_out_collect_name,out_to_name) as whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum as latest_warehousing_time_quantum --2021210新增揽收时间段
    ,branch_in_total_all_time
    ,main_total_all_time
    ,branch_out_total_all_time
    ,dt
from (
    select
         in_from_code
        ,update_date
        ,in_from_provider_code
        ,in_from_city_code
        ,in_from_area_code
        ,out_to_provider_code
        ,out_to_city_code
        ,out_to_area_code
        ,out_to_code
        ,in_branch_id
        ,main_id
        ,out_branch_id
        ,in_collect_code
        ,in_to_code
        ,start_center_code
        ,search_type
        ,start_network_code
        ,in_from_regional_code
        ,in_from_regional_desc
        ,in_from_financial_center_code
        ,in_from_financial_center_desc
        ,in_from_provider_desc
        ,in_from_city_desc
        ,in_from_area_desc
        ,if(length(in_collect_name)>1,in_collect_name,null) as in_collect_name
        ,if(length(in_from_name)>1,in_from_name,null) as in_from_name
        ,if(length(in_to_name)>1,in_to_name,null) as in_to_name
        ,in_edge_latest_warehousing
        ,in_edge_planned_departure
        ,in_edge_planned_arrival
        ,in_edge2_planned_departure
        ,in_edge2_planned_arrival
        ,branch_in_span_days
        ,branch_in_total_time
        ,is_main_route
        ,transfer_type
        ,num_of_transfer
        ,e1_center_flow
        ,e1_line_code
        ,e1_planned_departure
        ,e1_runtime
        ,e1_planned_arrival
        ,e1_e2_stop_time
        ,e2_center_flow
        ,e2_line_code
        ,e2_planned_departure
        ,e2_runtime
        ,e2_planned_arrival
        ,e2_e3_stop_time
        ,e3_center_flow
        ,e3_line_code
        ,e3_planned_departure
        ,e3_runtime
        ,e3_planned_arrival
        ,e3_e4_stop_time
        ,e4_center_flow
        ,e4_line_code
        ,e4_planned_departure
        ,e4_runtime
        ,e4_planned_arrival
        ,e4_e5_stop_time
        ,e5_center_flow
        ,e5_line_code
        ,e5_planned_departure
        ,e5_runtime
        ,e5_planned_arrival
        ,e5_e6_stop_time
        ,e6_center_flow
        ,e6_line_code
        ,e6_planned_departure
        ,e6_runtime
        ,e6_planned_arrival
        ,e6_e7_stop_time
        ,e1_end_code
        ,e2_end_code
        ,e3_end_code
        ,e4_end_code
        ,e5_end_code
        ,e6_end_code
        ,transfer_total_span_days
        ,transfer_total_time_use
        ,main_total_span_days
        ,main_total_time_use
        ,end_center
        ,end_center_code
        ,start_center
        ,out_from_code
        ,out_collect_code
        ,out_to_regional_desc
        ,out_to_regional_code
        ,out_to_financial_center_desc
        ,out_to_financial_center_code
        ,out_to_provider_desc
        ,out_to_city_desc
        ,out_to_area_desc
        ,if(length(out_collect_name)>1,out_collect_name,null) as out_collect_name
        ,if(length(out_to_name)>1,out_to_name,null) as out_to_name
        ,if(length(out_from_name)>1,out_from_name,null) as out_from_name
        ,out_has_collect
        ,out_edge_planned_departure
        ,out_edge_planned_arrival_time
        ,out_edge2_planned_departure
        ,out_edge2_planned_arrival_time
        ,out_edge2_latest_warehouse_time
        ,out_edge_latest_warehouse_time
        ,out_edge_span_days_sign
        ,out_edge2_span_days_sign
        ,out_edge_deadline_sign_time
        ,out_edge2_deadline_sign_time
        ,branch_out_span_days
        ,branch_out_total_time
        ,effective_date
        ,expiration_date
        ,branch_in_center_stop_time
        ,center_branch_out_stop_time
        ,branch_in_center_span_days
        ,center_branch_out_span_days
        ,is_circuitous
        ,out_edge_span_days_arrive
        ,out_edge2_span_days_arrive
        ,in_nodes
        ,out_nodes
        ,total_days_use
        ,total_time_use
        ,total_days_t
        ,total_nodes
        ,working_days
        ,extra_in_collect_code
        ,if(length(extra_in_collect_name)>1,extra_in_collect_name,null) as extra_in_collect_name
        ,extra_in_line_name
        ,extra_in_latest_warehousing
        ,extra_in_planned_departure
        ,extra_in_planned_arrival
        ,extra_out_collect_code
        ,if(length(extra_out_collect_name)>1,extra_out_collect_name,null) as extra_out_collect_name
        ,extra_out_line_name
        ,extra_out_latest_warehousing
        ,extra_out_planned_departure
        ,extra_out_planned_arrival
        ,in_network_line_name
        ,in_collect_line_name
        ,out_network_line_name
        ,out_collect_line_name
        ,town_plus_time
        ,branch_in_ship_time
        ,branch_in_ship_span_days
        ,branch_out_ship_time
        ,branch_out_ship_span_days
        ,all_line_name
        ,out_town_plus_time
        ,in_edge_run_time
        ,in_edge_span_days
        ,in_edge2_run_time
        ,in_edge2_span_days
        ,in_edge3_run_time
        ,in_edge3_span_days
        ,out_edge_run_time
        ,out_edge_span_days
        ,out_edge2_run_time
        ,out_edge2_span_days
        ,out_edge3_run_time
        ,out_edge3_span_days
        ,e1_span_days
        ,e2_span_days
        ,e3_span_days
        ,e4_span_days
        ,e5_span_days
        ,e6_span_days
        ,delivery_time
        ,route_flow
        ,in_edge2_latest_warehousing
        ,e1_latest_warehousing
        ,e2_latest_warehousing
        ,e3_latest_warehousing
        ,e4_latest_arrival_time
        ,e5_latest_arrival_time
        ,e6_latest_arrival_time
        ,start_taking_shift
        ,end_send_shift
        ,warehouse_end_time
        ,reserve_1
        ,reserve_2
        ,route_contain_main_line
        ,direct_route_type
        ,in_line_day
        ,out_line_day
        ,in_ship_span_days
        ,in_manage_region_code
        ,in_manage_region_name
        ,out_manage_region_code
        ,out_manage_region_name
        ,latest_warehousing_time_quantum as latest_warehousing_time_quantum --2021210新增揽收时间段
        ,network_detail_1.name as e1_end_name
        ,network_detail_2.name as e2_end_name
        ,network_detail_3.name as e3_end_name
        ,network_detail_4.name as e4_end_name
        ,network_detail_5.name as e5_end_name
        ,network_detail_6.name as e6_end_name
        ,branch_in_total_all_time
        ,main_total_all_time
        ,branch_out_total_all_time
        ,dt
    from (
        select *
        from jms_dm.dm_route_recommend --兜底路由
        where dt = '{{ execution_date | cst_ds }}'
    ) route_fastest
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_1 on network_detail_1.code = route_fastest.e1_end_code
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_2 on network_detail_2.code = route_fastest.e2_end_code
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_3 on network_detail_3.code = route_fastest.e3_end_code
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_4 on network_detail_4.code = route_fastest.e4_end_code
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_5 on network_detail_5.code = route_fastest.e5_end_code
    left join (
        select code,name
        from jms_dim.dim_network_whole_massage
    ) network_detail_6 on network_detail_6.code = route_fastest.e6_end_code
) a
distribute by pmod(hash(rand()),100);